﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.OleDb;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Business
{
    public partial class panel_ConsumptionCheck : UserControl
    {
        private INISettings mySetting;
        private SqlConnection myConnection;
        private SqlConnection masterConnection;
        private List<int> change_row;

        private List<string> rzcode_list;

        private DataTable StructureTable;

        private bool check;

        public panel_ConsumptionCheck()
        {
            InitializeComponent();
            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);
            this.masterConnection = new SqlConnection(mySetting.DB2ConnectionString);
            this.change_row = new List<int>();
            this.rzcode_list = new List<string>();
            this.StructureTable = new DataTable();
            this.StructureTable.Columns.Add("RobotNo", typeof(string));
            this.StructureTable.Columns.Add("PartNo", typeof(string));
            this.StructureTable.Columns.Add("Quantity", typeof(int));
            this.buttonConsumptionTransfer.Enabled = false;
            this.buttonAddExt.Enabled = false;
            this.gridExportList.BringToFront();

            this.check = false;
            this.documentDate.DateTime = DateTime.Now.Date;
            LoadData();
            this.ExportList.CellValueChanged += ExportList_CellValueChanged;

            this.ExportList.PopupMenuShowing += ExportList_PopupMenuShowing;
            this.exportConsumptionCheckContext.ItemClicked += exportConsumptionCheckContext_ItemClicked;
        }        

        void exportConsumptionCheckContext_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {
            this.buttonConsumptionTransfer.Enabled = false;
            this.exportConsumptionCheckContext.Hide();
            switch (e.ClickedItem.Name)
            {
                case "rzUpdateSelection":
                    RZUpdate();
                    break;
                case "consumptionCheckSelection":
                    ConsumptionCheck();
                    break;
                case "consumptionTransferSelection":
                    ConsumptionTransfer();
                    break;
                case "refreshSelection":
                    LoadData();
                    break;
            }
        }

        void ExportList_PopupMenuShowing(object sender, DevExpress.XtraGrid.Views.Grid.PopupMenuShowingEventArgs e)
        {
            this.exportConsumptionCheckContext.Show(MousePosition);
        }

        private void LoadData()
        {
            DeleteExtendedIQ();
            rzcode_list.Clear();
            change_row.Clear();
            check = false;
            this.buttonConsumptionTransfer.Enabled = false;
            DataSet sourceDataSet = new DataSet();            
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySetting.SQLConnectionString;
            string sql = "SELECT t1.[ECheck], t1.[ID], t1.[PlanNo], t1.[CustomerID], t2.[FullName], LTRIM(RTRIM(t1.[RobotNo])) AS RobotNo,"
                + " t1.[Quantity] - ISNULL(t3.RobotQuantity, 0) AS Quantity,"
                + " t1.[Quantity] - ISNULL(t3.RobotQuantity, 0) AS ConsumptionQuantity,"
                + " t1.[Exported], t1.[ExportedDate], t1.[RZCode], t1.[DocumentDate],"
                + " t1.[HaveStructure]"
                + " FROM [241_ExportPlan] AS t1 LEFT JOIN"
                + " [241_RZCode] AS t3 ON t1.PlanNo=t3.PlanNo LEFT JOIN"
                + " [222_Customer] AS t2 ON t1.[CustomerID]=t2.[CustomerID]"
                + " WHERE t1.[Quantity] - ISNULL(t3.RobotQuantity, 0) > 0"
                + " ORDER BY t1.RobotNo";
                //+ " ORDER BY t1.[PlanNo], t1.RobotNo";
            sourceDataSet = mySQLManagement.SQL_Query(sql);
            if (sourceDataSet != null)
            {
                gridExportList.DataSource = sourceDataSet.Tables[0];
                for (int i = 0; i < ExportList.DataRowCount; i++)
                {
                    DataRow row = ExportList.GetDataRow(i);
                    if ((bool)row[0])
                    {
                        change_row.Add(i);
                    }
                }
            }
            else
            {
                MessageBox.Show("There is no data for Export Check");
                DataTable newTable = new DataTable();
                newTable.Columns.Add("ECheck", typeof(bool));
                newTable.Columns.Add("ID", typeof(int));
                newTable.Columns.Add("PlanNo", typeof(string));
                newTable.Columns.Add("CustomerID", typeof(string));
                newTable.Columns.Add("CustomerName", typeof(string));
                newTable.Columns.Add("RobotNo", typeof(string));
                newTable.Columns.Add("Quantity", typeof(float));
                newTable.Columns.Add("ConsumptionQuantity", typeof(float));                
                newTable.Columns.Add("Exported", typeof(bool));
                newTable.Columns.Add("ExporttedDate", typeof(DateTime));
                newTable.Columns.Add("RZNo", typeof(string));
                newTable.Columns.Add("DocumentDate", typeof(DateTime));
                newTable.Columns.Add("HaveStructure", typeof(bool));
                gridExportList.DataSource = newTable;
            }
            ExportList.Columns[0].Caption = "ECheck";
            ExportList.Columns[1].Caption = "ID";
            ExportList.Columns[1].Visible = false;
            ExportList.Columns[2].Caption = "Plan No";
            ExportList.Columns[3].Caption = "Customer ID";
            ExportList.Columns[3].Visible = false;
            ExportList.Columns[4].Caption = "Customer Name";
            ExportList.Columns[5].Caption = "Robot No";
            ExportList.Columns[6].Caption = "Quantity";
            ExportList.Columns[7].Caption = "Consumption Quantity";            
            ExportList.Columns[8].Caption = "Exported";
            ExportList.Columns[9].Caption = "Exported Date";
            ExportList.Columns[10].Caption = "RZ Code";
            ExportList.Columns[11].Caption = "Document Date";
            ExportList.Columns[12].Caption = "Have Strcture";
            ExportList.BestFitColumns();            
            ExportList.Columns[1].OptionsColumn.AllowEdit = false;
            ExportList.Columns[2].OptionsColumn.AllowEdit = false;
            ExportList.Columns[3].OptionsColumn.AllowEdit = false;
            ExportList.Columns[4].OptionsColumn.AllowEdit = false;
            ExportList.Columns[5].OptionsColumn.AllowEdit = false;
            ExportList.Columns[6].OptionsColumn.AllowEdit = false;
            ExportList.Columns[8].OptionsColumn.AllowEdit = false;
            ExportList.Columns[9].OptionsColumn.AllowEdit = false;
            ExportList.Columns[10].OptionsColumn.AllowEdit = false;
            ExportList.Columns[11].OptionsColumn.AllowEdit = false;
            ExportList.Columns[12].OptionsColumn.AllowEdit = false;            
        }

        void ExportList_CellValueChanged(object sender, DevExpress.XtraGrid.Views.Base.CellValueChangedEventArgs e)
        {
            this.buttonConsumptionTransfer.Enabled = false;
            ExportList.PostEditor();
            ExportList.UpdateCurrentRow();
            DataRow row = ExportList.GetFocusedDataRow();
            if ((bool)row[0])
            {
                if (!change_row.Contains(ExportList.GetFocusedDataSourceRowIndex())) 
                {
                    change_row.Add(ExportList.GetFocusedDataSourceRowIndex());
                }
            }
            else
            {
                if (change_row.Contains(ExportList.GetFocusedDataSourceRowIndex()))
                {
                    change_row.Remove(ExportList.GetFocusedDataSourceRowIndex());
                }
            }
        }

        private void buttonRZUpdate_Click(object sender, EventArgs e)
        {
            DeleteExtendedIQ();
            RZUpdate();
            this.buttonAddExt.Enabled = true;
        }
        
        //////////////////////////////////////////////
        /// RZ Code Update
        //////////////////////////////////////////////        
        private void RZUpdate()
        {
            this.buttonConsumptionTransfer.Enabled = false;
            this.rzcode_list.Clear();
            string error = null;
            string masterCode = null;
            DateTime docDate = new DateTime();
            if (string.IsNullOrEmpty(this.masterRZCode.Text))
            {
                error = "Please enter Master RZ Code";
            }
            else
            {
                masterCode = this.masterRZCode.Text;
            }

            if (string.IsNullOrEmpty(error))
            {
                if (this.documentDate.EditValue == null)
                {
                    error = "Please select Document Date";
                }
                else
                {
                    docDate = DateTime.Parse(this.documentDate.EditValue.ToString());
                }
            }

            if (string.IsNullOrEmpty(error))
            {
                if (this.change_row.Count <= 0)
                {
                    error = "No plan selected to check";
                }
            }

            // Check if RZ Code exist
            if (string.IsNullOrEmpty(error))
            {
                try
                {
                    myConnection.Open();
                    SqlCommand cmd_sql = myConnection.CreateCommand();
                    int done = 0;
                    foreach (int i in change_row)
                    {
                        DataRow row = ExportList.GetDataRow(i);
                        cmd_sql.CommandText = "SELECT COUNT(ID) FROM [241_RZCode]"
                            + " WHERE LEFT(RZCode, 8)='" + masterCode + "'";
                        SqlDataReader dataReader = cmd_sql.ExecuteReader();
                        if (dataReader.Read())
                        {
                            if (dataReader[0] != DBNull.Value)
                            {
                                done += Int32.Parse(dataReader[0].ToString());
                            }
                        }
                        dataReader.Close();
                    }
                    cmd_sql.Dispose();
                    myConnection.Close();

                    if (done > 0)
                    {
                        error = "RZ code has been existed.";
                    }
                }
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show(ex.Message);
                }
            }
            
            // Update RZ Code
            if (string.IsNullOrEmpty(error))
            {
                try
                {
                    for (int i = 0; i < ExportList.RowCount; i++)
                    {
                        DataRow clear_row = ExportList.GetDataRow(i);
                        if (clear_row["RZCode"] != DBNull.Value)
                        {
                            clear_row.BeginEdit();
                            clear_row["RZCode"] = DBNull.Value;
                            clear_row["DocumentDate"] = DBNull.Value;                            
                            clear_row.EndEdit();
                        }
                    }
                    ExportList.PostEditor();

                    string rzcode = null;
                    change_row.Sort();
                    int index = 1;                    
                    foreach (int i in change_row)
                    {                        
                        rzcode = masterCode + "-" + index.ToString("D3");
                        DataRow row = ExportList.GetDataRow(ExportList.GetRowHandle(i));
                        if (row["RZCode"] == DBNull.Value)
                        {
                            row.BeginEdit();
                            row["RZCode"] = rzcode;
                            row["DocumentDate"] = docDate.Date;
                            row["ECheck"] = true;
                            row.EndEdit();
                            foreach (int j in change_row)
                            {
                                DataRow dub_row = ExportList.GetDataRow(ExportList.GetRowHandle(j));
                                if (dub_row["RobotNo"].ToString().Trim() == row["RobotNo"].ToString().Trim())
                                {
                                    if (dub_row["RZCode"] == DBNull.Value)
                                    {
                                        dub_row.BeginEdit();
                                        dub_row["RZCode"] = rzcode;
                                        dub_row["DocumentDate"] = docDate.Date;
                                        dub_row["ECheck"] = true;
                                        dub_row.EndEdit();
                                    }
                                }
                            }
                            this.rzcode_list.Add(rzcode);
                            index++;
                        }
                    }

                    ExportList.PostEditor();

                    MessageBox.Show("RZ Code update success.");

                    this.buttonAddExt.Enabled = true;
                }
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show(ex.Message);
                }
            }
            else
            {
                MessageBox.Show(error);
            }
        }

        private void buttonConsumptionCheck_Click(object sender, EventArgs e)
        {
            if (!check)
            {
                ConsumptionCheck();
            }
            else
            {
                if (MessageBox.Show("Do you want to calculate new data?", "Confirm calculate", MessageBoxButtons.YesNo) == DialogResult.Yes)
                {
                    ConsumptionCheck();
                }
                else
                {
                    CheckNegativeIQ();
                }
            }
        }

        /////////////////////////
        /// Consumption Check
        /////////////////////////
        private void ConsumptionCheck()
        {
            bool rzcheck = true;
            bool have_structure = false;
            string error = null;
            foreach (int i in change_row)
            {
                DataRow row = ExportList.GetDataRow(i);
                if (string.IsNullOrEmpty(row["RZCode"].ToString()))
                {
                    rzcheck = false;
                }
            }

            if (rzcheck)
            {
                have_structure = true;
                // Calculate structure for selected row
                DataTable final_structure_Table = new DataTable();
                final_structure_Table.Columns.Add("PLanNo", typeof(string));
                final_structure_Table.Columns.Add("RZCode", typeof(string));
                final_structure_Table.Columns.Add("RobotNo", typeof(string));
                final_structure_Table.Columns.Add("RobotQuantity", typeof(float));
                final_structure_Table.Columns.Add("PartNo", typeof(string));
                final_structure_Table.Columns.Add("MassQuantity", typeof(float));
                final_structure_Table.Columns.Add("NetQuantity", typeof(float));

                foreach (int i in change_row)
                {
                    DataRow row = ExportList.GetDataRow(i);
                    if (!string.IsNullOrEmpty(row["RobotNo"].ToString().Trim()))
                    {
                        DataTable temp_table = StructureCalculation(row["RobotNo"].ToString().Trim());
                        if (temp_table.Rows.Count > 0)
                        {
                            foreach (DataRow temp_row in temp_table.Rows)
                            {                                
                                DataRow add_row = final_structure_Table.NewRow();
                                add_row["PlanNo"] = row["PlanNo"];
                                add_row["RZCode"] = row["RZCode"];
                                add_row["RobotNo"] = temp_row["RobotNo"];
                                float robot_no = 0;
                                float.TryParse(row["ConsumptionQuantity"].ToString(), out robot_no);
                                add_row["RobotQuantity"] = robot_no;
                                add_row["PartNo"] = temp_row["PartNo"];                                
                                float mass_qty = 0;
                                float net_qty = 0;                                
                                float.TryParse(temp_row["MassQuantity"].ToString(), out mass_qty);
                                float.TryParse(temp_row["NetQuantity"].ToString(), out net_qty);
                                add_row["MassQuantity"] = robot_no * mass_qty;
                                if (net_qty == 0)
                                {
                                    add_row["NetQuantity"] = robot_no * mass_qty;
                                }
                                else
                                {
                                    add_row["NetQuantity"] = robot_no * net_qty;
                                }
                                final_structure_Table.Rows.Add(add_row);
                            }
                        }
                        else
                        {
                            have_structure = false;
                        }
                    }
                }
                
                if (have_structure)
                {
                    try
                    {
                        //Update Structure to database
                        string sql = null;
                        SqlCommand cmd;
                        SqlDataReader datareader;

                        myConnection.Open();

                        // Clear current database
                        
                        sql = "DELETE [241_ExportCheckSummary]";
                        cmd = new SqlCommand(sql, myConnection);
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();
                        
                        sql = "DELETE [241_ExportCheck]";
                        cmd = new SqlCommand(sql, myConnection);
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();

                        // Insert new database to calculate  
                        foreach (int i in change_row)
                        {
                            DataRow row = ExportList.GetDataRow(i);
                            cmd = myConnection.CreateCommand();
                            cmd.CommandText = "INSERT INTO [241_ExportCheckSummary] (RZCode, PlanNo, RobotNo, RobotQuantity)"
                                + " VALUES ('" + row["RZCode"].ToString() + "', '" + row["PlanNo"].ToString() + "',"
                                + " N'" + row["RobotNo"].ToString() + "', " + float.Parse(row["ConsumptionQuantity"].ToString()) + ")";
                            cmd.ExecuteNonQuery();
                            cmd.Dispose();
                        }

                        foreach (DataRow structure_row in final_structure_Table.Rows)
                        {
                            cmd = myConnection.CreateCommand();
                            cmd.CommandText = "INSERT INTO [241_ExportCheck] (PlanNo, RZCode, RobotNo,"
                                + " PartNo, MassQuantity, NetQuantity) VALUES ('" + structure_row["PlanNo"].ToString() + "',"
                                + " '" + structure_row["RZCode"].ToString() + "', N'" + structure_row["RobotNo"].ToString() + "',"
                                + " N'" + structure_row["PartNo"].ToString() + "',"
                                + " " + float.Parse(structure_row["MassQuantity"].ToString()) + ","
                                + " " + float.Parse(structure_row["NetQuantity"].ToString()) + ")";
                            cmd.ExecuteNonQuery();
                            cmd.Dispose();
                        }                        

                        // Check null IQ
                        sql = "SELECT COUNT (*) FROM [VRZIQNullCheck]";
                        cmd = new SqlCommand(sql, myConnection);
                        datareader = cmd.ExecuteReader();
                        if (datareader.Read())
                        {
                            if (datareader[0] != DBNull.Value)
                            {
                                int nullIQ = 0;
                                Int32.TryParse(datareader[0].ToString(), out nullIQ);
                                if (nullIQ > 0)
                                {
                                    error = "There are some part have no IQID. Please check";
                                }
                            }                            
                        }
                        datareader.Close();
                        cmd.Dispose();

                        if (string.IsNullOrEmpty(error))
                        {
                            sql = "DELETE [241_ConsumptionAdjust]";
                            cmd = new SqlCommand(sql, myConnection);
                            cmd.ExecuteNonQuery();
                            cmd.Dispose();

                            sql = "INSERT INTO [241_ConsumptionAdjust] (RZCode, IQID, MassQuantity, NetQuantity, AdjustedMass,"
                                + " RobotQuantity, IQUnit)"
                                + " SELECT RZCode, IQID, MassQuantity, NetQuantity, AdjustedMass, RobotQuantity, IQUnit"
                                + " FROM VConsumptionAdjust";
                            cmd = new SqlCommand(sql, myConnection);
                            cmd.CommandTimeout = 10000;
                            cmd.ExecuteNonQuery();
                            cmd.Dispose();                            
                        }
                        myConnection.Close();

                        if (!string.IsNullOrEmpty(error))
                        {
                            MessageBox.Show(error);
                        }
                        else
                        {
                            CheckNegativeIQ();
                        }

                    }
                    catch (Exception ex)
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        MessageBox.Show(ex.Message);
                    }

                    check = true;
                }
                else
                {
                    MessageBox.Show("There are some robot has no Structure. Please check");
                } 
            }
            else
            {
                MessageBox.Show("There are some plan has no RZ Code");
            }
           
        }

        /// <summary>
        /// Check Negative IQ
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void CheckNegativeIQ()
        {
            string err = null;
            bool negative = false;
            try
            {
                myConnection.Open();
                // Check negative IQID
                string sql = "SELECT COUNT(IQID) FROM VConsumptionAdjustedCheck WHERE AdjustedRemain < 0";
                SqlCommand cmd = new SqlCommand(sql, myConnection);
                cmd.CommandTimeout = 10000;
                SqlDataReader datareader = cmd.ExecuteReader();
                if (datareader.Read())
                {
                    int negativeIQ = 0;
                    Int32.TryParse(datareader[0].ToString(), out negativeIQ);
                    if (negativeIQ > 0)
                    {
                        negative = true;
                    }
                }
                datareader.Close();
                cmd.Dispose();
                myConnection.Close();
            }
            catch (Exception ex)
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                err = ex.Message;
            }

            if (string.IsNullOrEmpty(err))
            {
                if (negative)
                {
                    MessageBox.Show("There are some IQID do not enough quantity to export. Please adjust.");
                    frm_Adjust_NegativeIQ adjust_form = new frm_Adjust_NegativeIQ();
                    frm_Check_NegativeIQ check_form = new frm_Check_NegativeIQ();
                    adjust_form.Show();
                    check_form.Show();
                }
                else
                {
                    this.buttonConsumptionTransfer.Enabled = true;
                    MessageBox.Show("Consumption check OK. You can Transfer");
                }
            }
            else
            {
                MessageBox.Show(err);
            }
        }

        private void buttonConsumptionTransfer_Click(object sender, EventArgs e)
        {
            ConsumptionTransfer();
            DeleteExtendedIQ();
            this.buttonAddExt.Enabled = false;
        }


        //////////////////////////////////////
        /// Consumption Transfer
        //////////////////////////////////////
        private void ConsumptionTransfer()
        {
            string error = null;            
            DateTime docDate = new DateTime();            

            if (string.IsNullOrEmpty(error))
            {
                if (this.documentDate.EditValue == null)
                {
                    error = "Please select Document Date";
                }
                else
                {
                    docDate = DateTime.Parse(this.documentDate.EditValue.ToString());
                }
            }

            if (string.IsNullOrEmpty(error))
            {
                if (this.change_row.Count <= 0)
                {
                    error = "No plan selected to check";
                }
            }

            if (string.IsNullOrEmpty(error))
            {
                myConnection.Open();
                SqlCommand cmd_sql;
                try
                {
                    foreach (int i in change_row)
                    {
                        DataRow row = this.ExportList.GetDataRow(i);

                        // Insert RZ Code
                        cmd_sql = myConnection.CreateCommand();
                        cmd_sql.CommandText = "INSERT INTO [241_RZCode] (PlanNo, RZCode, RobotNo, RobotQuantity, Date, ConsumpTrans)"
                            + " VALUES ('" + row[2].ToString() + "', '" + row[10].ToString() + "',"
                            + " '" + row[5].ToString() + "', " + row[7].ToString() + ", @docdate, 1)";
                        cmd_sql.Parameters.Add("@docdate", SqlDbType.DateTime).Value = docDate.Date;
                        cmd_sql.ExecuteNonQuery();
                        cmd_sql.Dispose();

                        /*
                        // Update ExportPlan Doc Finished                        
                        cmd_sql = myConnection.CreateCommand();
                        cmd_sql.CommandText = "UPDATE [241_ExportPlan] SET DocFinished = 1"
                            + " WHERE ID=" + row[1].ToString();
                        cmd_sql.ExecuteNonQuery();
                        cmd_sql.Dispose(); */
                    }

                    // Insert RZConsumption
                    cmd_sql = myConnection.CreateCommand();
                    cmd_sql.CommandText = "INSERT INTO [241_RZConsumption] (RZCode, IQID, TotalQuantity,"
                        + " MassQuantity, NetQuantity)"
                        + " SELECT RZCode, IQID, RobotQuantity, AdjustedMass,"
                        + " NetQuantity * (AdjustedMass/MassQuantity) AS NetQuantity"
                        + " FROM [241_ConsumptionAdjust]";
                    cmd_sql.ExecuteNonQuery();
                    cmd_sql.Dispose();

                    cmd_sql = myConnection.CreateCommand();
                    cmd_sql.CommandText = "DELETE [241_ConsumptionAdjust]";
                    cmd_sql.ExecuteNonQuery();
                    cmd_sql.Dispose();

                    myConnection.Close();
                    this.buttonConsumptionTransfer.Enabled = false;
                    MessageBox.Show("Consumption Transfer success!");
                }
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show(ex.Message);
                }
            }
            else
            {
                MessageBox.Show(error);
            }
        }

        private void buttonRefresh_Click(object sender, EventArgs e)
        {
            LoadData();
        }
        
        ////////////////////////////////////
        /// Calculate Structure Final
        ///////////////////////////////////
        private DataTable StructureCalculation(string robotno)
        {   
            DataTable final_sum_Table = new DataTable();
            final_sum_Table.Columns.Add("RobotNo", typeof(string));
            final_sum_Table.Columns.Add("PartNo", typeof(string));
            final_sum_Table.Columns.Add("MassQuantity", typeof(float));
            final_sum_Table.Columns.Add("NetQuantity", typeof(float));

            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                SqlDataReader dataReader;

                cmd_sql.CommandText = "SELECT * FROM [222_RobotEndStructure] WHERE RobotNo='" + robotno + "'";
                dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    DataRow new_row = final_sum_Table.NewRow();
                    new_row["RobotNo"] = dataReader["RobotNo"].ToString().Trim();
                    new_row["PartNo"] = dataReader["PartNo"].ToString().Trim();
                    float mass_quantity = 0;
                    mass_quantity = float.Parse(dataReader["MassQuantity"].ToString().Trim());
                    new_row["MassQuantity"] = mass_quantity;
                    float net_quantity = 0;
                    net_quantity = float.Parse(dataReader["NetQuantity"].ToString().Trim());
                    new_row["NetQuantity"] = net_quantity;                    
                    final_sum_Table.Rows.Add(new_row);
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch (Exception ex)
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show(ex.Message);
                //MessageBox.Show("Cannot get structure");
            }            
            return final_sum_Table;             
        }

        ///////////////////////////////////////////////
        /// Delete Extended IQ
        //////////////////////////////////////////////
        private void DeleteExtendedIQ()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "DELETE FROM [241_ExportCheckEx]";
                cmd_sql.ExecuteNonQuery();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch (Exception ex)
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show(ex.Message);
                //MessageBox.Show("Cannot get structure");
            }
        }

        private void buttonAddExt_Click(object sender, EventArgs e)
        {
            if (this.rzcode_list.Count > 0)
            {
                frm_ExtendIQ extend_iq = new frm_ExtendIQ();
                extend_iq.rzcode_list = this.rzcode_list;
                extend_iq.ShowDialog();
            }
            else
            {
                MessageBox.Show("There is no RZCode for Extend IQ");
            }
        }
    }
}
